package com.stj.dao;

import com.stj.entity.Blog;
import com.stj.entity.Link;
import com.stj.entity.blogType;
import com.stj.entity.comment;
import com.stj.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BlogDao {
    Connection conn = JdbcUtil.getConnection();
    public List getBlogList() {
        List list=new ArrayList();
        try {
            String sql="SELECT * FROM t_blog ORDER BY releaseDate DESC";
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            while (rs.next()){
                Blog blog=new Blog(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getDate(4), rs.getInt(5), rs.getInt(6), rs.getString(7));
                list.add(blog);
            }
        }catch (Exception e){
                System.out.println("BlogDao ->getBlogList 方法错误"+e);
        }
        return  list;
    }

    public List blogTypeCountList() {
            List blogtypelist =new ArrayList();
            try{
                String sql="SELECT * FROM `t_blogtype`  ORDER BY id DESC";
                PreparedStatement pst = conn.prepareStatement(sql);
                ResultSet rs = pst.executeQuery();
                while (rs.next()){
                    blogType blogtype = new blogType(rs.getInt(1),rs.getString(2),rs.getInt(3));
                    blogtypelist.add(blogtype);
                }
            }catch (Exception e){
                System.out.println("BlogDao ->blogService 方法错误" +e);
            }

        return  blogtypelist;
    }

    public List linkList() {
        List link =new ArrayList();
        try{
            String sql="SELECT * from t_link  ORDER BY id DESC";
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            while (rs.next()){
                Link linkEntity =new Link(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4));
                link.add(linkEntity);
            }
        }catch (Exception e){
            System.out.println("BlogDao ->linkList 方法错误"+e);
        }
    return  link;
    }

    public Blog getBlogByIdList(String id) {
        try {
            String sql = "SELECT * FROM `t_blog`,t_blogtype WHERE t_blog.typeId = t_blogtype.id AND t_blog.id=" + id;
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                /*博文信息*/
                Blog blog = new Blog(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getDate(4), rs.getInt(5), rs.getInt(6), rs.getString(7));
                /*博文类型*/
                blogType blogType = new blogType(rs.getInt("id"), rs.getString("typeName"), rs.getInt("orderNo"));
                blog.setBlogType(blogType);
                return blog;
            }
        } catch (Exception e) {
            System.out.println("BlogDao ->getBlogByIdList 方法错误"+e);
        }
        return  null;
    }

    public List getBlogByNameList(String q) {

        ArrayList searchBlogList =new ArrayList();
        try {
            String sql =" SELECT * FROM t_blog   WHERE   t_blog.content LIKE '%"+q+"%' OR t_blog.title LIKE '%"+q+"%'" ;
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();

            while (rs.next()){
                /*博文信息*/
                Blog  SearchBlog = new Blog(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getDate(4), rs.getInt(5), rs.getInt(6), rs.getString(7));
                searchBlogList.add(SearchBlog);

            }
            return searchBlogList;
        } catch (Exception e) {
            System.out.println("BlogDao ->getBlogByNameList 方法错误"+e);
        }
        return  null;
    }
    /*添加博客*/
    public int addBlog(Blog blog) {
        int  i;
        try{
            String sql = "insert into t_blog(title,summary,content,typeId) VALUES(?,?,?,?)";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1,blog.getTitle());
            pst.setString(2,blog.getSummary());
            pst.setString(3,blog.getContent());
            pst.setInt(4,blog.getTypeId());
            i = pst.executeUpdate();
            return i;
        }catch (Exception e){
            System.out.println("BlogDao ->addBlog 方法错误"+e);
        }
       return  0;
    }
    /*发表评论*/
    public int addComment(comment comment) {
        int  i = 0;
        try{
            String sql = "insert into t_comment(blogId,content,`userIp`,`state`) VALUES(?,?,?,?)";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1,comment.getBlogId());
            pst.setString(2,comment.getContent());
            pst.setString(3,comment.getUserIp());
            pst.setInt(4,comment.getState());
            i =pst.executeUpdate();
            return i;
    }catch (Exception e){
            System.out.println("BlogDao ->addComment 方法错误"+e);
        }
        return i;
    }
    /*查询所有评论*/
    public List selectComment(int id) {
        ArrayList list = new ArrayList();
        try{
            String sql = "select * from t_comment WHERE blogId =" +id;
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs =pst.executeQuery();

            while (rs.next()){
                /*int blogId, String content, String userIp, Date createData, int statu*/
                comment comment = new comment(rs.getInt(3),rs.getString(4),rs.getString(2),rs.getTimestamp(5),rs.getInt(6));
                list.add(comment);
            }
        }catch (Exception e){
            System.out.println("BlogDao ->addComment 方法错误"+e);
        }
        return list;
    }

    public List getBlogTypeList(int id) {
        ArrayList list = new ArrayList();
      try {
          String sql = "select * from t_blog WHERE typeId =" +id;
          PreparedStatement pst = conn.prepareStatement(sql);
          ResultSet rs =pst.executeQuery();
          while (rs.next()){
              Blog  getBlogTypeList = new Blog(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getDate(4), rs.getInt(5), rs.getInt(6), rs.getString(7));
              list.add(getBlogTypeList);
          }

      }catch (Exception e){
          System.out.println("BlogDao ->getBlogTypeList 方法错误"+e);
      }
      return  list;
    }
    /*改变阅读量*/
    public void addReplyHit(int blogId, int replyHit) {
        try {
            Connection conn = JdbcUtil.getConnection();
            String sql = "UPDATE t_blog SET replyHit= ? WHERE id =?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1,replyHit+1);
            pst.setInt(2,blogId);
            pst.executeUpdate();
        } catch (Exception e) {
            System.out.println("BlogDao ->addReplyHit 方法错误"+e);
        }
    }
}
